{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create entry points to spark\n",
    "try:\n",
    "    sc.stop()\n",
    "except:\n",
    "    pass\n",
    "from pyspark import SparkContext, SparkConf\n",
    "from pyspark.sql import SparkSession\n",
    "sc=SparkContext()\n",
    "spark = SparkSession(sparkContext=sc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Boolean column expression\n",
    "\n",
    "Create a column expression that will return **boolean values**. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Example data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+----+---+-----+---+----+-----+-----+---+---+----+----+\n",
      "|              model| mpg|cyl| disp| hp|drat|   wt| qsec| vs| am|gear|carb|\n",
      "+-------------------+----+---+-----+---+----+-----+-----+---+---+----+----+\n",
      "|          Mazda RX4|21.0|  6|160.0|110| 3.9| 2.62|16.46|  0|  1|   4|   4|\n",
      "|      Mazda RX4 Wag|21.0|  6|160.0|110| 3.9|2.875|17.02|  0|  1|   4|   4|\n",
      "|         Datsun 710|22.8|  4|108.0| 93|3.85| 2.32|18.61|  1|  1|   4|   1|\n",
      "|     Hornet 4 Drive|21.4|  6|258.0|110|3.08|3.215|19.44|  1|  0|   3|   1|\n",
      "|  Hornet Sportabout|18.7|  8|360.0|175|3.15| 3.44|17.02|  0|  0|   3|   2|\n",
      "|            Valiant|18.1|  6|225.0|105|2.76| 3.46|20.22|  1|  0|   3|   1|\n",
      "|         Duster 360|14.3|  8|360.0|245|3.21| 3.57|15.84|  0|  0|   3|   4|\n",
      "|          Merc 240D|24.4|  4|146.7| 62|3.69| 3.19| 20.0|  1|  0|   4|   2|\n",
      "|           Merc 230|22.8|  4|140.8| 95|3.92| 3.15| 22.9|  1|  0|   4|   2|\n",
      "|           Merc 280|19.2|  6|167.6|123|3.92| 3.44| 18.3|  1|  0|   4|   4|\n",
      "|          Merc 280C|17.8|  6|167.6|123|3.92| 3.44| 18.9|  1|  0|   4|   4|\n",
      "|         Merc 450SE|16.4|  8|275.8|180|3.07| 4.07| 17.4|  0|  0|   3|   3|\n",
      "|         Merc 450SL|17.3|  8|275.8|180|3.07| 3.73| 17.6|  0|  0|   3|   3|\n",
      "|        Merc 450SLC|15.2|  8|275.8|180|3.07| 3.78| 18.0|  0|  0|   3|   3|\n",
      "| Cadillac Fleetwood|10.4|  8|472.0|205|2.93| 5.25|17.98|  0|  0|   3|   4|\n",
      "|Lincoln Continental|10.4|  8|460.0|215| 3.0|5.424|17.82|  0|  0|   3|   4|\n",
      "|  Chrysler Imperial|14.7|  8|440.0|230|3.23|5.345|17.42|  0|  0|   3|   4|\n",
      "|           Fiat 128|32.4|  4| 78.7| 66|4.08|  2.2|19.47|  1|  1|   4|   1|\n",
      "|        Honda Civic|30.4|  4| 75.7| 52|4.93|1.615|18.52|  1|  1|   4|   2|\n",
      "|     Toyota Corolla|33.9|  4| 71.1| 65|4.22|1.835| 19.9|  1|  1|   4|   1|\n",
      "+-------------------+----+---+-----+---+----+-----+-----+---+---+----+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars = spark.read.csv('../../data/mtcars.csv', inferSchema=True, header=True)\n",
    "mtcars = mtcars.withColumnRenamed('_c0', 'model')\n",
    "mtcars.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `between()`: true/false if the column value is between a given range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'((cyl >= 4) AND (cyl <= 6))'>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mpg_between = mtcars.cyl.between(4,6)\n",
    "mpg_between"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------------------------+\n",
      "|cyl|((cyl >= 4) AND (cyl <= 6))|\n",
      "+---+---------------------------+\n",
      "|  6|                       true|\n",
      "|  6|                       true|\n",
      "|  4|                       true|\n",
      "|  6|                       true|\n",
      "|  8|                      false|\n",
      "+---+---------------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.cyl, mpg_between).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `contains()`: true/false if the column value contains a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'contains(model, Ho)'>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_contains = mtcars.model.contains('Ho')\n",
    "model_contains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+-------------------+\n",
      "|            model|contains(model, Ho)|\n",
      "+-----------------+-------------------+\n",
      "|        Mazda RX4|              false|\n",
      "|    Mazda RX4 Wag|              false|\n",
      "|       Datsun 710|              false|\n",
      "|   Hornet 4 Drive|               true|\n",
      "|Hornet Sportabout|               true|\n",
      "+-----------------+-------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.model, model_contains).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `endswith()`: true/false if the column value ends with a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'endswith(model, t)'>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_endswith = mtcars.model.endswith('t')\n",
    "model_endswith"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+------------------+\n",
      "|            model|endswith(model, t)|\n",
      "+-----------------+------------------+\n",
      "|        Mazda RX4|             false|\n",
      "|    Mazda RX4 Wag|             false|\n",
      "|       Datsun 710|             false|\n",
      "|   Hornet 4 Drive|             false|\n",
      "|Hornet Sportabout|              true|\n",
      "|          Valiant|              true|\n",
      "+-----------------+------------------+\n",
      "only showing top 6 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.model, model_endswith).show(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `isNotNull()`: true/false if the column value is not Null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|height| name|\n",
      "+------+-----+\n",
      "|    80|  Tom|\n",
      "|  null|Alice|\n",
      "+------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import Row\n",
    "df = spark.createDataFrame([Row(name='Tom', height=80), Row(name='Alice', height=None)])\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'(height IS NOT NULL)'>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "height_isNotNull = df.height.isNotNull()\n",
    "height_isNotNull"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+--------------------+\n",
      "|height|(height IS NOT NULL)|\n",
      "+------+--------------------+\n",
      "|    80|                true|\n",
      "|  null|               false|\n",
      "+------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df.height, height_isNotNull).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `isNull()`: true/false if the column value is Null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'(height IS NULL)'>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "height_isNull = df.height.isNull()\n",
    "height_isNull"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----------------+\n",
      "|height|(height IS NULL)|\n",
      "+------+----------------+\n",
      "|    80|           false|\n",
      "|  null|            true|\n",
      "+------+----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df.height, height_isNull).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `isin()`: true/false if the column value is contained by the evaluated argument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'(carb IN (2, 3))'>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "carb_isin = mtcars.carb.isin([2, 3])\n",
    "carb_isin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----------------+\n",
      "|carb|(carb IN (2, 3))|\n",
      "+----+----------------+\n",
      "|   4|           false|\n",
      "|   4|           false|\n",
      "|   1|           false|\n",
      "|   1|           false|\n",
      "|   2|            true|\n",
      "|   1|           false|\n",
      "|   4|           false|\n",
      "|   2|            true|\n",
      "|   2|            true|\n",
      "|   4|           false|\n",
      "+----+----------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.carb, carb_isin).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `like()`: true/false if the column value matches a pattern based on a _SQL LIKE_ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'model LIKE Ho%'>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_like = mtcars.model.like('Ho%')\n",
    "model_like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+--------------+\n",
      "|            model|model LIKE Ho%|\n",
      "+-----------------+--------------+\n",
      "|        Mazda RX4|         false|\n",
      "|    Mazda RX4 Wag|         false|\n",
      "|       Datsun 710|         false|\n",
      "|   Hornet 4 Drive|          true|\n",
      "|Hornet Sportabout|          true|\n",
      "|          Valiant|         false|\n",
      "|       Duster 360|         false|\n",
      "|        Merc 240D|         false|\n",
      "|         Merc 230|         false|\n",
      "|         Merc 280|         false|\n",
      "+-----------------+--------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.model, model_like).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `rlike()`: true/false if the column value matches a pattern based on a _SQL RLIKE_ (LIKE with Regex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'model RLIKE t$'>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_rlike = mtcars.model.rlike('t$')\n",
    "model_rlike"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+--------------+\n",
      "|              model|model RLIKE t$|\n",
      "+-------------------+--------------+\n",
      "|          Mazda RX4|         false|\n",
      "|      Mazda RX4 Wag|         false|\n",
      "|         Datsun 710|         false|\n",
      "|     Hornet 4 Drive|         false|\n",
      "|  Hornet Sportabout|          true|\n",
      "|            Valiant|          true|\n",
      "|         Duster 360|         false|\n",
      "|          Merc 240D|         false|\n",
      "|           Merc 230|         false|\n",
      "|           Merc 280|         false|\n",
      "|          Merc 280C|         false|\n",
      "|         Merc 450SE|         false|\n",
      "|         Merc 450SL|         false|\n",
      "|        Merc 450SLC|         false|\n",
      "| Cadillac Fleetwood|         false|\n",
      "|Lincoln Continental|         false|\n",
      "|  Chrysler Imperial|         false|\n",
      "|           Fiat 128|         false|\n",
      "|        Honda Civic|         false|\n",
      "|     Toyota Corolla|         false|\n",
      "+-------------------+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.model, model_rlike).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `startswith()`: true/false if the column value starts with a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'startswith(model, Merc)'>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_startswith = mtcars.model.startswith('Merc')\n",
    "model_startswith"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-----------------------+\n",
      "|              model|startswith(model, Merc)|\n",
      "+-------------------+-----------------------+\n",
      "|          Mazda RX4|                  false|\n",
      "|      Mazda RX4 Wag|                  false|\n",
      "|         Datsun 710|                  false|\n",
      "|     Hornet 4 Drive|                  false|\n",
      "|  Hornet Sportabout|                  false|\n",
      "|            Valiant|                  false|\n",
      "|         Duster 360|                  false|\n",
      "|          Merc 240D|                   true|\n",
      "|           Merc 230|                   true|\n",
      "|           Merc 280|                   true|\n",
      "|          Merc 280C|                   true|\n",
      "|         Merc 450SE|                   true|\n",
      "|         Merc 450SL|                   true|\n",
      "|        Merc 450SLC|                   true|\n",
      "| Cadillac Fleetwood|                  false|\n",
      "|Lincoln Continental|                  false|\n",
      "|  Chrysler Imperial|                  false|\n",
      "|           Fiat 128|                  false|\n",
      "|        Honda Civic|                  false|\n",
      "|     Toyota Corolla|                  false|\n",
      "+-------------------+-----------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "mtcars.select(mtcars.model, model_startswith).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
